package com.lqz.tools;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;

import freemarker.core.ParseException;
import freemarker.template.Configuration;
import freemarker.template.MalformedTemplateNameException;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.TemplateNotFoundException;
import freemarker.template.Version;

public class CreateMethod {

	private String[] colnames; // 列名数组
	private String[] jdbcColNames; //数据库列名数组
	private String[] javaColTypes; // java列名类型数组
	private String[] jdbcColTypes; // 列名类型数组
	private int[] colSizes; // 列名大小数组
	
	public CreateMethod(DataBaseConnPojo dbcp)
			throws SQLException, ClassNotFoundException, IOException, TemplateException {
		// 加载驱动
		Class.forName(dbcp.getDataBaseDriver());
		// 连续数据库
		Connection connection = DriverManager.getConnection(dbcp.getDataBaseFullUrl(),
				dbcp.getUserName(), dbcp.getUserPassword());
		PreparedStatement pstmt = null;
		String strsql = "select * from " + dbcp.getTableName();
		try {
			pstmt = connection.prepareStatement(strsql);
			ResultSetMetaData rsmd = pstmt.getMetaData();
			int size = rsmd.getColumnCount(); // 共有多少列
			dbcp.setColSize(size);
			colnames = new String[size];
			javaColTypes = new String[size];
			jdbcColTypes = new String[size];
			colSizes = new int[size];
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				colnames[i] = CreateMethod.getCamelStr(rsmd.getColumnName(i + 1));
				jdbcColNames[i]=rsmd.getColumnName(i + 1);
				jdbcColTypes[i] = rsmd.getColumnTypeName(i + 1);
				javaColTypes[i] = this.sqlType2JavaType(jdbcColTypes[i]);
				if (jdbcColTypes[i].equalsIgnoreCase("datetime") || jdbcColTypes[i].equalsIgnoreCase("date")) {
					dbcp.setImportUtil(true);
				}
				if (jdbcColTypes[i].equalsIgnoreCase("image") || jdbcColTypes[i].equalsIgnoreCase("text")) {
					dbcp.setImportSql(true);
				}
				colSizes[i] = rsmd.getColumnDisplaySize(i + 1);
			}
			dbcp.setColnames(colnames);
			dbcp.setJavaColTypes(javaColTypes);
			dbcp.setJdbcColTypes(jdbcColTypes);
			dbcp.setColSizes(colSizes);
			
			// 获取项目路径
			String projectPath = System.getProperty("user.dir");
			String packagePath = projectPath + "/src/" + dbcp.getPackageName().replaceAll("\\.", "/");
			String pagePath = projectPath + "/WebContent/" + dbcp.getPackageName().replaceAll("\\.", "/");


			dbcp.setProjectPath(projectPath);
			dbcp.setPackagePath(packagePath);
			dbcp.setPagePath(pagePath);
			try {
				// 创建Freemarker配置实例
				Configuration cfg = new Configuration(new Version("2.3.25"));
				cfg.setDirectoryForTemplateLoading(new File(projectPath + "/src/com/lqz/template"));
				//实体类名称
				dbcp.setPojoName(initcap(dbcp.getTableName()));	
				
				//实体类路径
				File pojoFile = new File(
						dbcp.getPackagePath() + "/" + getCamelStr(dbcp.getTableName()) + "/model");
				if (!pojoFile.exists()) {
					pojoFile.mkdirs();
				}
				dbcp.setPojoPackagePath(pojoFile.toString());
				//实体类包名
				dbcp.setPojoPackageName(dbcp.getPackageName() + "." + getCamelStr(dbcp.getTableName()) + ".model");
				//获取主键
				DatabaseMetaData databaseMetaData = connection.getMetaData();
				ResultSet pkRSet = databaseMetaData.getPrimaryKeys(dbcp.getDataBaseName(), dbcp.getUserName(),dbcp.getTableName());
				String sqlTablePK=null;
				while(pkRSet.next()){
					if(pkRSet.getObject(6).toString().equalsIgnoreCase("PRIMARY")){
						sqlTablePK=pkRSet.getObject(4).toString();
					}
						
				}
				for(int i=0;i<colnames.length;i++){
					if(sqlTablePK.equalsIgnoreCase(colnames[i])){
						dbcp.setSqlTablePK(sqlTablePK);dbcp.setSqlTablePK(sqlTablePK);
						dbcp.setJavaTablePK(getCamelStr(sqlTablePK));
						dbcp.setSqlTablePKType(jdbcColNames[i]);
						dbcp.setJavaTablePKType(sqlType2JavaType(jdbcColNames[i]));
					}
				}
				
				
				pojo(dbcp, cfg);
				pojoQuery(dbcp, cfg);
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// DatabaseUtils.closeDatabase(conn, pstmt, null);
		}
	}
	// 生成dao层
	public static void createDAO(HashMap<String, Object> rootMap, Configuration cfg) throws TemplateNotFoundException,
	MalformedTemplateNameException, ParseException, IOException, TemplateException {
		// 加载模板文件
		Template t = cfg.getTemplate("dao.ftl");
		File daoPath = new File(
				rootMap.get("packagePath") + "/" + getCamelStr(rootMap.get("tableName").toString()) + "/dao");
		rootMap.put("pojoPackagePath",daoPath.toString());
		if (!daoPath.exists()) {
			daoPath.mkdirs();
		}
		File file = new File(daoPath.toString() + "/" + initcap(rootMap.get("tableName").toString()) + "Mapper.java");
		System.out.println(file.getAbsolutePath());
		OutputStream out = new FileOutputStream(file);
		BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
		t.process(rootMap, rd);
		rd.close();
		out.flush();
		out.close();
	}
	// 生成实体类
	public static void pojo(DataBaseConnPojo dbcp, Configuration cfg) throws TemplateNotFoundException,
			MalformedTemplateNameException, ParseException, IOException, TemplateException {
		// 加载模板文件
		Template t = cfg.getTemplate("pojo.ftl");
		File file = new File(dbcp.getPojoPackagePath() + "/" + dbcp.getPojoName()+ ".java");
		OutputStream out = new FileOutputStream(file);
		BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
		t.process(dbcp, rd);
		System.out.println(file.getAbsolutePath());
		rd.close();
		out.flush();
		out.close();
	}
	// 生成实体查询类
	public static void pojoQuery(DataBaseConnPojo dbcp, Configuration cfg) throws TemplateNotFoundException,
	MalformedTemplateNameException, ParseException, IOException, TemplateException {
		// 加载模板文件
		Template t = cfg.getTemplate("pojoQuery.ftl");
		File file = new File(dbcp.getPojoPackagePath() + "/" + dbcp.getPojoName() + "Query.java");
		System.out.println(file.getAbsolutePath());
		OutputStream out = new FileOutputStream(file);
		BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
		t.process(dbcp, rd);
		rd.close();
		out.flush();
		out.close();
	}

	// 生成mapper
	public static void mapper(HashMap<String, Object> rootMap, Configuration cfg) throws TemplateNotFoundException,
			MalformedTemplateNameException, ParseException, IOException, TemplateException {
		// 加载模板文件
		Template t = cfg.getTemplate("mapper.ftl");
		File file = new File(
				rootMap.get("packagePath") + "/" + getCamelStr(rootMap.get("tableName").toString()) + "/model");
		if (!file.exists()) {
			file.mkdirs();
		}
		rootMap.put("pojoPath",
				rootMap.get("packageName") + "." + getCamelStr(rootMap.get("tableName").toString()) + ".model");
		File file1 = new File(file.toString() + "/" + initcap(rootMap.get("tableName").toString()) + ".java");
		System.out.println(file1.getAbsolutePath());
		OutputStream out = new FileOutputStream(file1);
		BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
		t.process(rootMap, rd);
		rd.close();
		out.flush();
		out.close();
	}
	// 生成controller文件
		public static void controllerJava(HashMap<String, Object> rootMap, Configuration cfg) throws TemplateNotFoundException,
				MalformedTemplateNameException, ParseException, IOException, TemplateException {
			// 加载模板文件
			Template t = cfg.getTemplate("controller.ftl");
			File file = new File(
					rootMap.get("packagePath") + "/" + getCamelStr(rootMap.get("tableName").toString()) + "/controller");
			if (!file.exists()) {
				file.mkdirs();
			}
			rootMap.put("controllerPath",
					rootMap.get("packageName") + "." + getCamelStr(rootMap.get("tableName").toString()) + ".controller");
			File file1 = new File(file.toString() + "/" + initcap(rootMap.get("tableName").toString()) + ".java");
			OutputStream out = new FileOutputStream(file1);
			BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
			t.process(rootMap, rd);
			rd.close();
			out.flush();
			out.close();
		}
	// 生成查看页面
	public static void shwoJsp(HashMap<String, Object> rootMap, Configuration cfg) throws TemplateNotFoundException,
			MalformedTemplateNameException, ParseException, IOException, TemplateException {
		// 加载模板文件
		Template t = cfg.getTemplate("show.ftl");
		File file = new File("D:/jsp/src/" + rootMap.get("fileName") + ".jsp");
		OutputStream out = new FileOutputStream(file);
		BufferedWriter rd = new BufferedWriter(new OutputStreamWriter(out, "utf-8"));
		t.process(rootMap, rd);
		rd.close();
		out.flush();
		out.close();
	}

	/**
	 * 把输入字符串的首字母改成大写
	 * 
	 * @param str
	 * @return
	 */
	private static String initcap(String str) {
		char[] ch = str.toCharArray();
		if (ch[0] >= 'a' && ch[0] <= 'z') {
			ch[0] = (char) (ch[0] - 32);
		}
		return getCamelStr(new String(ch));
	}

	// 例：user_name --> userName
	private static String getCamelStr(String s) {
		while (s.indexOf("_") > 0) {
			int index = s.indexOf("_");
			// System.out.println(s.substring(index+1, index+2).toUpperCase());
			s = s.substring(0, index) + s.substring(index + 1, index + 2).toUpperCase() + s.substring(index + 2);
		}
		return s;
	}

	private String sqlType2JavaType(String sqlType) {
		if (sqlType.equalsIgnoreCase("bit")) {
			return "bool";
		} else if (sqlType.equalsIgnoreCase("tinyint")) {
			return "byte";
		} else if (sqlType.equalsIgnoreCase("smallint")) {
			return "short";
		} else if (sqlType.equalsIgnoreCase("int") || sqlType.equalsIgnoreCase("integer")) {
			return "int";
		} else if (sqlType.equalsIgnoreCase("bigint")) {
			return "long";
		} else if (sqlType.equalsIgnoreCase("float")) {
			return "float";
		} else if (sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric")
				|| sqlType.equalsIgnoreCase("real")) {
			return "double";
		} else if (sqlType.equalsIgnoreCase("money") || sqlType.equalsIgnoreCase("smallmoney")) {
			return "double";
		} else if (sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char")
				|| sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar")) {
			return "String";
		} else if (sqlType.equalsIgnoreCase("datetime") || sqlType.equalsIgnoreCase("date")) {
			return "Date";
		} else if (sqlType.equalsIgnoreCase("image")) {
			return "Blob";
		} else if (sqlType.equalsIgnoreCase("text")) {
			return "Clob";
		}
		return "String";
	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, TemplateException {
		// new CreateMethod();
		DataBaseConnPojo dbcp = new DataBaseConnPojo();
		dbcp.setDataBaseName("proc");
		dbcp.setTableName("users");
		dbcp.setPackageName("com.test");
		dbcp.setDataBaseFullUrl(dbcp.getDataBaseUrl() + dbcp.getDataBaseName()
				+ "?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true");
		new CreateMethod(dbcp);
	}
}
